******************************************************************************
****						Appendix Table G.5 - MOST COMMON VEHICLES 
****							BY DECILE OF TRAVEL TIME SAVINGS		*******
*******************************************************************************
use .\data\clean\I10W_laneuse_dataset_15nov14_wcensus, clear
drop if holiday==1
drop if dow==0 | dow==6
*create frequency
keep if inlist(hour,5,6,7,8) /*period=="AM Peak"*/
keep if acct_type=="PRIVATE"
drop if TTdiff_ELSpeed<=0
gen pay_trip=charged_toll>0&charged_toll~=.
gen hov_trip=charged_toll==0
gen trip=charged_toll~=.

collapse (sum) pay_trip hov_trip trip, by(acct_no month)
drop if acct_no==""
sort acct_no month
by acct_no: gen temp=_n
drop if temp==1
collapse (mean) pay_trip hov_trip trip, by(acct_no)

gen pay_bin=0
replace pay_bin=1 if 1>=pay_trip&pay_trip>0
replace pay_bin=2 if 5>=pay_trip&pay_trip>1
replace pay_bin=3 if 10>=pay_trip&pay_trip>5
replace pay_bin=4 if 15>=pay_trip&pay_trip>10
replace pay_bin=5 if 20>=pay_trip&pay_trip>15
replace pay_bin=6 if 10000>=pay_trip&pay_trip>20

sort acct_no
tempfile acct_category
save `acct_category', replace


use ".\data\clean\laneuse_dataset_19aug14.dta", clear
keep if acct_type=="PRIVATE"
sort acct_no
merge m:1 acct_no using .\data\clean\ZIP_accts, keep(1 3) nogen
sort zip_code
merge m:1 zip_code using .\data\clean\censusdata, keep(1 3) nogen
sort acct_no
merge m:1 acct_no using .\data\clean\vehicles_used, keep(1 3) nogen


keep if inlist(hour,5,6,7,8) /*period=="AM Peak"*/
gen WTP=charged_toll/(TTdiff_ELSpeed/60)
gen temp1=WTP<0
gen wage=mean_inc/2/2040
gen Price_per_mile=charged_toll/dist
g miles=cond(dist<2,"0-2", ///
	cond(dist<5, "2-5", ///
	cond(dist<7, "5-7", "7-10.5")))
	
*create frequency
sort acct_no
merge m:1 acct_no using `acct_category'
keep if WTP>0&WTP~=.
xtile decile = TTdiff_ELSpeed if WTP>0&WTP~=., nq(10)

g count=1
egen decile_count=sum(count), by(decile)
g decile_shr=1/decile_count

g space=" - "
replace vehicle_make=strproper(vehicle_make)
replace vehicle_model=strproper(vehicle_model)
egen make_model=concat(vehicle_make space vehicle_model)
set more off


preserve
	capture drop count
	g count=1
	collapse (sum) count, by(make_model decile)
	gsort decile -count 
	egen num=seq(), by(decile)
	drop if num>3

	duplicates drop make_model decile, force
	keep num make_model decile
	reshape wide make_model, i(decile) j(num)
	tempfile temp 
	save `temp'
	restore, preserve
	capture drop count
	g count=1
	collapse (sum) count, by(make_model)
	gsort -count 
	egen num=seq()
	drop if num>3
	duplicates drop make_model, force
	keep make_model num
	g decile=11
	reshape wide make_model, i(decile) j(num)
	append using `temp'
	sort decile
	export excel ".\results\appendix\AppendixFigsTabs.xlsx", sheetmodify sheet("Appendix Table G.11")
restore
